package CreateCTAxls;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import xls.Utils;

public class CreateCTAXLS
{

  /**
 * @author wed
 * This executable creates the resources hyperlinks for each language based on the pass description
 * Please note to change the correct worksheet for creating links
 * XLS columns S, T, U, V, W
 * 
   */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
    	Workbook workbook = Workbook.getWorkbook(new File("C:\\Documents and Settings\\Administrator\\My Documents\\Dropbox\\CTA\\XLS\\CTA.xls"));
    	Sheet sheet = workbook.getSheet(15);
    	Cell[] count = sheet.getColumn(0);
    	WritableWorkbook outputXLS = Workbook.createWorkbook(new File("C:\\Documents and Settings\\Administrator\\My Documents\\Dropbox\\CTA\\XLS\\XLSBuild.xls"));
    	WritableSheet outputSheet = outputXLS.createSheet("First Sheet", 0);

    	
    	
    	
    	
    	
    	for (int i = 1; i < count.length; i++) {
    		
    		Cell numberCell = sheet.getCell(0,i);
    		Label numberLabel = new Label(0,i,numberCell.getContents());

    		Cell latlngXCell = sheet.getCell(1,i);
    		Label latlngXLabel = new Label(1,i,latlngXCell.getContents());
    		
    		Cell latlngYCell = sheet.getCell(2,i);
    		Label latlngYLabel = new Label(2,i,latlngYCell.getContents());
    		
    		Cell passNameCell = sheet.getCell(3,i);
	        String passName = passNameCell.getContents();
	        Label passNameLabel = new Label(3,i,passName); 
    		
    		Cell passNameENCell = sheet.getCell(4,i);
	        String passNameEN = passNameENCell.getContents();
	        Label passNameENLabel = new Label(4,i,passNameEN);
	        
    		Cell passNameDECell = sheet.getCell(5,i);
	        String passNameDE = passNameDECell.getContents();
	        Label passNameDELabel = new Label(5,i,passNameDE);
	        
    		Cell passNameNLCell = sheet.getCell(6,i);
	        String passNameNL = passNameNLCell.getContents();
	        Label passNameNLLabel = new Label(6,i,passNameNL);
	        
    		Cell passNameITCell = sheet.getCell(7,i);
	        String passNameIT = passNameITCell.getContents();
	        Label passNameITLabel = new Label(7,i,passNameIT);
	        
    		Cell passNameFRCell = sheet.getCell(8,i);
	        String passNameFR = passNameFRCell.getContents();
	        Label passNameFRLabel = new Label(8,i,passNameFR);
	        
    		Cell siteNameCell = sheet.getCell(9,i);
	        String siteNames = siteNameCell.getContents();
	        Label siteNamesLabel = new Label(9,i,siteNames);
	        
	        String passDescription = passName.replaceFirst("passName", "passDescription");
	        Label passDescriptionLabel = new Label(10, i, passDescription); 
	        
	        Cell passDescriptionEN = sheet.getCell(11,i);
	        Label passDescriptionENLabel = new Label(11,i,passDescriptionEN.getContents());
	        
	        Cell passDescriptionDE = sheet.getCell(12,i);
	        Label passDescriptionDELabel = new Label(12,i,passDescriptionDE.getContents());

	        Cell passDescriptionNL = sheet.getCell(13,i);
	        Label passDescriptionNLLabel = new Label(13,i,passDescriptionNL.getContents());

	        Cell passDescriptionIT = sheet.getCell(14,i);
	        Label passDescriptionITLabel = new Label(14,i,passDescriptionIT.getContents());

	        Cell passDescriptionFR = sheet.getCell(15,i);
	        Label passDescriptionFRLabel = new Label(15,i,passDescriptionFR.getContents());

	        String earthName = passName.replaceAll("passName", "");
	        Label route = new Label(16, i, "http://sites.google.com/a/cyclingthealps.com/cyclingthealps/routes/" + earthName + ".kmz?attredirects=0&d=1");
	        
	        String webElement = passName.replaceFirst("passName", "webElement");
	        Label webElementLabel = new Label(17, i, webElement);
	        
	        String replacedEN = Utils.replaceUmlautsHtml(passNameEN);
	        String heightremovedEN = replacedEN.substring(0, replacedEN.length()-8);
	        String hyphenatedEN = heightremovedEN.replaceAll(" ", "%20");
	        Label webElmentEN = new Label(18, i, "<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversation%20" + hyphenatedEN + "' scrolling='no' allowtransparency='true'></iframe>"); 
	        
	        String replacedDE = Utils.replaceUmlautsHtml(passNameDE);
	        String heightremovedDE = replacedDE.substring(0, replacedDE.length()-8);
	        String hyphenatedDE  = heightremovedDE.replaceAll(" ", "%20");
	        Label webElmentDE = new Label(19, i, "<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Konversation%20" + hyphenatedDE + "' scrolling='no' allowtransparency='true'></iframe>"); 

	        String replacedNL = Utils.replaceUmlautsHtml(passNameNL);
	        String heightremovedNL = replacedNL.substring(0, replacedNL.length()-8);
	        String hyphenatedNL  = heightremovedNL.replaceAll(" ", "%20");
	        Label webElmentNL = new Label(20, i, "<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversatie%20" + hyphenatedNL + "' scrolling='no' allowtransparency='true'></iframe>");
	       
	        String replacedIT = Utils.replaceUmlautsHtml(passNameIT);
	        String heightremovedIT = replacedIT.substring(0, replacedIT.length()-8);
	        String hyphenatedIT  = heightremovedIT.replaceAll(" ", "%20");
	        Label webElmentIT = new Label(21, i, "<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversazione%20" + hyphenatedIT + "' scrolling='no' allowtransparency='true'></iframe>"); 

	        String replacedFR = Utils.replaceUmlautsHtml(passNameFR);
	        String heightremovedFR = replacedFR.substring(0, replacedFR.length()-8);
	        String hyphenatedFR  = heightremovedFR.replaceAll(" ", "%20");
	        Label webElmentFR = new Label(22, i, "<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Discuter%20" + hyphenatedFR + "' scrolling='no' allowtransparency='true'></iframe>"); 

			Cell BikemapCell = sheet.getCell(23, i);
			String bikemap = BikemapCell.getContents();
			Label linkBikemap = new Label(23,i,"");
			if (bikemap.isEmpty()) {
				linkBikemap.setString("<div style='width:600px;font-family:Arial,Helvetica,sans-serif;font-size:9px;color:#535353;background-color:#ffffff;font-style:normal;text-align:right;padding:0px;padding-bottom:3px !important;'><iframe width='600' height='424' border='0' src='http://www.bikemap.net/route/REPLACE/widget?width=600&amp;height=300&amp;extended=true&amp;maptype=3&amp;unit=km&amp;redirect=no' frameborder='0' marginheight='0' marginwidth='0' scrolling='no'></iframe><br />Bike route <a style='color:#2a88ac; text-decoration:underline;' href='http://www.bikemap.net/route/REPLACE'>REPLACE</a></div>");	
			}else {
				linkBikemap.setString("<div style='width:600px;font-family:Arial,Helvetica,sans-serif;font-size:9px;color:#535353;background-color:#ffffff;font-style:normal;text-align:right;padding:0px;padding-bottom:3px !important;'><iframe width='600' height='424' border='0' src='http://www.bikemap.net/route/" + bikemap +"/widget?width=600&amp;height=300&amp;extended=true&amp;maptype=3&amp;unit=km&amp;redirect=no' frameborder='0' marginheight='0' marginwidth='0' scrolling='no'></iframe><br />Bike route <a style='color:#2a88ac; text-decoration:underline;' href='http://www.bikemap.net/route/" + bikemap +"'>" + bikemap +"</a></div>");
			}
	        
			Cell StreetviewCell = sheet.getCell(24, i);
			String streetview = StreetviewCell.getContents();
	        Label streetviewLabel = new Label(24,i,streetview);
	        
			Cell MotorbikeCell = sheet.getCell(25, i);
			String motorbike = MotorbikeCell.getContents();
			Label linkMotorBike = new Label(25,i,"");
			if (motorbike.endsWith("NOP")) {
				linkMotorBike.setString("NOP");
			}else {
		        linkMotorBike.setString("<object width='640' height='505'><param name='movie' value='http://www.youtube.com/v/" + motorbike + "&hl=de_DE&fs=1&'></param><param name='allowFullScreen' value='true'></param><param name='allowscriptaccess' value='always'></param><embed src='http://www.youtube.com/v/" + motorbike + "&hl=de_DE&fs=1&' type='application/x-shockwave-flash' allowscriptaccess='always' allowfullscreen='true' width='640' height='505'></embed></object>");				
			}

	        Label ds = new Label(26,i, "90");

	        Cell climbbybikeCell = sheet.getCell(27,i);
	        Label climbbybikeLabel = new Label(27,i,climbbybikeCell.getContents());
	        
	        Cell quaeldichCell = sheet.getCell(28,i);
	        Label quaeldichLabel = new Label(28,i,quaeldichCell.getContents());
	        
	        Label video = new Label(29,i, "NOP");
	        
	        Label slideShow = new Label(30,i, "NOP");

	        Cell cyclingchallengeCell = sheet.getCell(31,i);
	        Label cyclingchallengeLabel = new Label(31,i,cyclingchallengeCell.getContents());
	        
	        Label tour = new Label(32, i, "http://sites.cyclingthealps.com/other/downloads/3Dtour" + earthName +".kmz?attredirects=0&d=1");
	        
	        String passLink = passName.replaceFirst("passName", "passLink");
	        Label passLinkLabel = new Label(34, i, passLink);
	        
	        Label hyperLinkEN = new Label(35, i, siteNames); 
	        
	        Label hyperLinkDE = new Label(36, i, siteNames);
	        
	        Label hyperLinkNL = new Label(37, i, siteNames);
	        
	        Label hyperLinkIT = new Label(38, i, siteNames);
	        
	        Label hyperLinkFR = new Label(39, i, siteNames);
	        
	        String passOracle = passName.replaceFirst("passName", "passOracle");
	        Label passOracleLabel = new Label(40, i, passOracle);
	        
    		Cell latBegin = sheet.getCell(41,i);
    		Cell longBegin = sheet.getCell(42,i);
    		Cell latEnd = sheet.getCell(43,i);
    		Cell lonngEnd = sheet.getCell(44,i);
	        String latB = latBegin.getContents();
	        String longB = longBegin.getContents();
	        String latE = latEnd.getContents();
	        String longE = lonngEnd.getContents();
	        Label latBeginLabel = new Label(41, i, latB);
	        Label longBeginLabel = new Label(42, i, longB);
	        Label latEndLabel = new Label(43, i, latE);
	        Label longEndLabel = new Label(44, i, longE);
	        
	        Label passStreetviewLabel = new Label(45, i, "");
	        Label streetView = new Label(46,i,"");
	        Label streetviewReverse = new Label(47,i,"");
	        
	        if (streetview.endsWith("NOP")) {
	        	passStreetviewLabel.setString("NOP");
	        	streetView.setString("NOP");
	        	streetviewReverse.setString("NOP");
	        }else {
	        	String passStreetview = passName.replaceFirst("passName", "passStreetview");
	        	passStreetviewLabel.setString(passStreetview);
	        	streetView.setString("<iframe src='http://s3.tripgeo.com/dirmap/map.htm?from="+ latB + ", " + longB + "&to=" + latE + ", "+ longE + "&fi=2000' width='100%' height='100%' style='padding:0px' marginwidth='0' marginheight='0' frameborder='0' scrolling='no'></iframe>");
	        	streetviewReverse.setString("<iframe src='http://s3.tripgeo.com/dirmap/map.htm?from="+ latE + ", " + longE + "&to=" + latB + ", "+ longB + "&fi=2000' width='100%' height='100%' style='padding:0px' marginwidth='0' marginheight='0' frameborder='0' scrolling='no'></iframe>");
	        }
	        
	        
	        Label tourReverse = new Label(48, i, "https://sites.google.com/a/cyclingthealps.com/cyclingthealps/other/downloadsreverse/3Dtour" + earthName + "Reverse.kmz?attredirects=0&d=1");
	        Label Andras = new Label(49,i,"NOP");
	        
    		Cell distanceCell = sheet.getCell(50,i);
	        Label distanceLabel = new Label(50,i,distanceCell.getContents());
	        
    		Cell threesixty = sheet.getCell(51,i);
	        Label threesixtyLabel = new Label(51,i,threesixty.getContents());
	        
	        try {
	        	outputSheet.addCell(numberLabel);
	        	outputSheet.addCell(latlngXLabel);
	        	outputSheet.addCell(latlngYLabel);
	        	outputSheet.addCell(passNameLabel);
	        	outputSheet.addCell(passNameENLabel);
	        	outputSheet.addCell(passNameDELabel);
	        	outputSheet.addCell(passNameNLLabel);
	        	outputSheet.addCell(passNameITLabel);
	        	outputSheet.addCell(passNameFRLabel);
	        	outputSheet.addCell(siteNamesLabel);
	        	outputSheet.addCell(passDescriptionLabel);
	        	outputSheet.addCell(passDescriptionENLabel);
	        	outputSheet.addCell(passDescriptionDELabel);
	        	outputSheet.addCell(passDescriptionNLLabel);
	        	outputSheet.addCell(passDescriptionITLabel);
	        	outputSheet.addCell(passDescriptionFRLabel);
				outputSheet.addCell(route);
				outputSheet.addCell(webElementLabel);
				outputSheet.addCell(webElmentEN);
				outputSheet.addCell(webElmentDE);
				outputSheet.addCell(webElmentNL);
				outputSheet.addCell(webElmentIT);
				outputSheet.addCell(webElmentFR);
				outputSheet.addCell(linkBikemap);
				outputSheet.addCell(streetviewLabel);
				outputSheet.addCell(linkMotorBike);
				outputSheet.addCell(ds);
				outputSheet.addCell(climbbybikeLabel);
				outputSheet.addCell(quaeldichLabel);
				outputSheet.addCell(video);
				outputSheet.addCell(slideShow);
				outputSheet.addCell(cyclingchallengeLabel);
				outputSheet.addCell(tour);
				outputSheet.addCell(passLinkLabel);
				outputSheet.addCell(hyperLinkEN);
				outputSheet.addCell(hyperLinkDE);
				outputSheet.addCell(hyperLinkNL);
				outputSheet.addCell(hyperLinkIT);
				outputSheet.addCell(hyperLinkFR);
				outputSheet.addCell(passOracleLabel);
				outputSheet.addCell(latBeginLabel);
				outputSheet.addCell(longBeginLabel);
				outputSheet.addCell(latEndLabel);
				outputSheet.addCell(longEndLabel);
				outputSheet.addCell(passStreetviewLabel);
				outputSheet.addCell(streetView);
				outputSheet.addCell(streetviewReverse);
				outputSheet.addCell(tourReverse);
				outputSheet.addCell(Andras);
				outputSheet.addCell(distanceLabel);
				outputSheet.addCell(threesixtyLabel);
				
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
	        
	    }
    	outputXLS.write(); 
    	try {
			outputXLS.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    } catch (BiffException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}
